package com.warrior.superdata.util;

import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.warrior.superdata.mysql.entity.dto.ExcelDataDto;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

@Slf4j
public class ExcelUtil {

    public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";

    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";

    /**
     * 获取合并单元格的值
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }
        return null;
    }

    /**
     * 判断合并了行
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    private boolean isMergedRow(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row == firstRow && row == lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param sheet
     * @param row    行下标
     * @param column 列下标
     * @return
     */
    private boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 判断sheet页中是否含有合并单元格
     *
     * @param sheet
     * @return
     */
    private boolean hasMerged(Sheet sheet) {
        return sheet.getNumMergedRegions() > 0 ? true : false;
    }

    /**
     * 合并单元格
     *
     * @param sheet
     * @param firstRow 开始行
     * @param lastRow  结束行
     * @param firstCol 开始列
     * @param lastCol  结束列
     */
    private void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }

    /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    public String getCellValue(Cell cell) {

        if (cell == null) {
            return "";
        }

        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {

            return cell.getStringCellValue();

        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {

            return String.valueOf(cell.getBooleanCellValue());

        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {

            return cell.getCellFormula();

        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {

            return String.valueOf(cell.getNumericCellValue());

        }
        return "";
    }

    /**
     * 从excel读取内容
     */
    public static void readContent(String fileName) {
        boolean isE2007 = false;    //判断是否是excel2007格式
        if (fileName.endsWith("xlsx")) {
            isE2007 = true;
        }
        try {
            InputStream input = new FileInputStream(fileName);  //建立输入流
            Workbook wb = null;
            //根据文件格式(2003或者2007)来初始化
            if (isE2007) {
                wb = new XSSFWorkbook(input);
            } else {
                wb = new HSSFWorkbook(input);
            }
            Sheet sheet = wb.getSheetAt(0);     //获得第一个表单
            Iterator<Row> rows = sheet.rowIterator(); //获得第一个表单的迭代器
            while (rows.hasNext()) {
                Row row = rows.next();  //获得行数据
                System.out.println("Row #" + row.getRowNum());  //获得行号从0开始
                Iterator<Cell> cells = row.cellIterator();    //获得第一行的迭代器
                while (cells.hasNext()) {
                    Cell cell = cells.next();
                    System.out.println("Cell #" + cell.getColumnIndex());
                    switch (cell.getCellType()) {   //根据cell中的类型来输出数据
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            System.out.println(cell.getNumericCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_STRING:
                            System.out.println(cell.getStringCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                            System.out.println(cell.getBooleanCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_FORMULA:
                            System.out.println(cell.getCellFormula());
                            break;
                        default:
                            System.out.println("unsuported sell type=======" + cell.getCellType());
                            break;
                    }
                }
            }
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * read the Excel .xlsx,.xls
     *
     * @param file jsp中的上传文件
     * @return
     * @throws IOException
     */
    public static List<List<String>> readExcel(MultipartFile file, boolean readFirstSheet) throws IOException {
        if (file == null || "".equals(file.getOriginalFilename().trim())) {
            return null;
        } else {
            String postfix = getPostfix(file.getOriginalFilename());
            if (StringUtils.isNotEmpty(postfix)) {
                if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    return readXls(file, readFirstSheet);
                } else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                    return readXlsx(file, readFirstSheet);
                } else {
                    return null;
                }
            }
        }
        return null;
    }

    public static List<List<String>> readExcel(MultipartFile file, Integer sheetNum) throws IOException {
        if (file == null || "".equals(file.getOriginalFilename().trim())) {
            return null;
        } else {
            String postfix = getPostfix(file.getOriginalFilename());
            if (StringUtils.isNotEmpty(postfix)) {
                if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    return readXls(file, sheetNum);
                } else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                    return readXlsx(file, sheetNum);
                } else {
                    return null;
                }
            }
        }
        return null;
    }

    public static List<List<String>> readXls(MultipartFile file, Integer sheetNum) {
        List<List<String>> list = new ArrayList<>();
        // IO流读取文件
        InputStream input = null;
        HSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new HSSFWorkbook(input);
            sheetNum = sheetNum == null ? 1 : sheetNum;
            //读取sheet(页)
            HSSFSheet hssfSheet = wb.getSheetAt(sheetNum);
            if (hssfSheet == null) {
                return list;
            }
            int totalRows = hssfSheet.getLastRowNum();
            //读取Row,从第二行开始
            for (int rowNum = 1; rowNum <= totalRows; rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    rowList = new ArrayList<>();
                    int totalCells = hssfRow.getLastCellNum();
                    //读取列，从第一列开始
                    for (short c = 0; c <= totalCells + 1; c++) {
                        HSSFCell cell = hssfRow.getCell(c);
                        if (cell == null) {
                            rowList.add("");
                            continue;
                        }
                        rowList.add(getHValue(cell).trim());
                    }
                    list.add(rowList);
                }
            }
        } catch (IOException e) {
            log.error("========== 导入xls文件出错 msg:{}  ============", e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                if (null != input) {
                    input.close();
                }
            } catch (IOException e) {
                log.error("========== 导入xls文件流关闭异常 msg:{}  ============", e.getMessage());
            }
        }
        return list;
    }

    /**
     * read the Excel 2003-2007 .xls
     *
     * @param file
     * @return
     * @throws IOException
     */
    public static List<List<String>> readXls(MultipartFile file, boolean readFirstSheet) {
        List<List<String>> list = new ArrayList<>();
        // IO流读取文件
        InputStream input = null;
        HSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new HSSFWorkbook(input);
            int sheetNum = 1;
            if (!readFirstSheet) {
                sheetNum = wb.getNumberOfSheets();
            }
            //读取sheet(页)
            for (int numSheet = 0; numSheet < sheetNum; numSheet++) {
                HSSFSheet hssfSheet = wb.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                int totalRows = hssfSheet.getLastRowNum();
                //读取Row,从第二行开始
                for (int rowNum = 1; rowNum <= totalRows; rowNum++) {
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow != null) {
                        rowList = new ArrayList<>();
                        int totalCells = hssfRow.getLastCellNum();
                        //读取列，从第一列开始
                        for (short c = 0; c <= totalCells + 1; c++) {
                            HSSFCell cell = hssfRow.getCell(c);
                            if (cell == null) {
                                rowList.add("");
                                continue;
                            }
                            rowList.add(getHValue(cell).trim());
                        }
                        list.add(rowList);
                    }
                }
            }

        } catch (IOException e) {
            log.error("========== 导入xls文件出错 msg:{}  ============", e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                if (null != input) {
                    input.close();
                }
            } catch (IOException e) {
                log.error("========== 导入xls文件流关闭异常 msg:{}  ============", e.getMessage());
            }
        }
        return list;
    }

    public static List<List<String>> readXlsx(MultipartFile file, Integer sheetNum) {
        List<List<String>> list = new ArrayList<>();
        // IO流读取文件
        InputStream input = null;
        XSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new XSSFWorkbook(input);
            sheetNum = sheetNum == null ? 1 : sheetNum;
            //读取sheet(页)
            XSSFSheet xssfSheet = wb.getSheetAt(sheetNum);
            if (xssfSheet == null) {
                return list;
            }
            int totalRows = xssfSheet.getLastRowNum();
            //读取Row,从第二行开始
            for (int rowNum = 1; rowNum <= totalRows; rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                    rowList = new ArrayList<String>();
                    int totalCells = xssfRow.getLastCellNum();
                    //读取列，从第一列开始
                    for (int c = 0; c <= totalCells + 1; c++) {
                        XSSFCell cell = xssfRow.getCell(c);
                        if (cell == null) {
                            rowList.add("");
                            continue;
                        }
                        rowList.add(getXValue(cell).trim());
                    }
                    list.add(rowList);
                } else {
                    break;
                }
            }
        } catch (IOException e) {
            log.error("========== 导入xlsx文件出错 msg:{}  ============", e.getMessage());
        } finally {
            try {
                if (null != input) {
                    input.close();
                }
            } catch (IOException e) {
                log.error("========== 导入xlsx文件流关闭异常 msg:{}  ============", e.getMessage());
            }
        }
        return list;
    }


    /**
     * read the Excel 2010 .xlsx
     *
     * @param file
     * @return
     * @throws IOException
     */
    public static List<List<String>> readXlsx(MultipartFile file, boolean readFirstSheet) {
        List<List<String>> list = new ArrayList<>();
        // IO流读取文件
        InputStream input = null;
        XSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new XSSFWorkbook(input);
            int sheetNum = 1;
            if (!readFirstSheet) {
                sheetNum = wb.getNumberOfSheets();
            }

            //读取sheet(页)
            for (int numSheet = 0; numSheet < sheetNum; numSheet++) {
                XSSFSheet xssfSheet = wb.getSheetAt(numSheet);
                if (xssfSheet == null) {
                    continue;
                }
                int totalRows = xssfSheet.getLastRowNum();
                //读取Row,从第二行开始
                for (int rowNum = 1; rowNum <= totalRows; rowNum++) {
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    if (xssfRow != null) {
                        rowList = new ArrayList<String>();
                        int totalCells = xssfRow.getLastCellNum();
                        //读取列，从第一列开始
                        for (int c = 0; c <= totalCells + 1; c++) {
                            XSSFCell cell = xssfRow.getCell(c);
                            if (cell == null) {
                                rowList.add("");
                                continue;
                            }
                            rowList.add(getXValue(cell).trim());
                        }
                        list.add(rowList);
                    } else {
                        break;
                    }
                }
            }
        } catch (IOException e) {
            log.error("========== 导入xlsx文件出错 msg:{}  ============", e.getMessage());
        } finally {
            try {
                if (null != input) {
                    input.close();
                }
            } catch (IOException e) {
                log.error("========== 导入xlsx文件流关闭异常 msg:{}  ============", e.getMessage());
            }
        }
        return list;
    }

    public static String getHValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
            String cellValue = "";
            DecimalFormat df = new DecimalFormat("#.##");
            cellValue = df.format(hssfCell.getNumericCellValue());
            String strArr = cellValue.substring(cellValue.lastIndexOf(".") + 1, cellValue.length());
            if (strArr.equals("00")) {
                cellValue = cellValue.substring(0, cellValue.lastIndexOf("."));
            }
            return cellValue;
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    /**
     * 单元格格式
     *
     * @param xssfCell
     * @return
     */
    public static String getXValue(XSSFCell xssfCell) {
        if (xssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else if (xssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            String cellValue = "";
            DecimalFormat df = new DecimalFormat("#.##");
            cellValue = df.format(xssfCell.getNumericCellValue());
            String strArr = cellValue.substring(cellValue.lastIndexOf(".") + 1, cellValue.length());
            if (strArr.equals("00")) {
                cellValue = cellValue.substring(0, cellValue.lastIndexOf("."));
            }
            return cellValue;
        } else {
            return String.valueOf(xssfCell.getStringCellValue());
        }
    }

    public static String getPostfix(String path) {
        if (StringUtils.isNotEmpty(path) && path.contains(".")) {
            return path.substring(path.lastIndexOf(".") + 1, path.length());
        }
        return "";
    }

    public static String exportExcel(ExcelDataDto data, String filePath) {
        XSSFWorkbook wb = null;
        FileOutputStream fileOutputStream = null;
        try {
            String sheetName = data.getName();
            if (null == sheetName) {
                sheetName = "Sheet1";
            }
            String filename = data.getFileName();
            wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet(sheetName);
            writeExcel(wb, sheet, data);
            fileOutputStream = new FileOutputStream(filePath + "/" + filename);
            wb.write(fileOutputStream);
        } catch (Exception e) {
            log.error("======= 报表导出异常 -> data:{},msg:{} =======", data, e.toString());
        } finally {
            try {
                if (null != fileOutputStream) {
                    fileOutputStream.close();
                }
                if (null != wb) {
                    wb.close();
                }
            } catch (Exception e) {
                log.error("关流异常 errMsg:{}", e.getMessage());
            }
        }
        return filePath;
    }

    public static XSSFWorkbook generateWb(ExcelDataDto data) {
        XSSFWorkbook wb = null;
        try {
            String sheetName = data.getName();
            if (null == sheetName) {
                sheetName = "Sheet1";
            }
            wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet(sheetName);
            writeExcel(wb, sheet, data);
        } catch (Exception e) {
            log.error("获取wb异常 errMsg:{}", e.getMessage());
        }
        return wb;
    }

    public static void writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelDataDto data) {
        int rowIndex = 0;
        rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles(), rowIndex);
        writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
        autoSizeColumns(sheet, data.getTitles().size() + 1);
    }

    private static void autoSizeColumns(Sheet sheet, int columnNumber) {
        for (int i = 0; i < columnNumber; i++) {
            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i, true);
            int colWidth = sheet.getColumnWidth(i) * 2;

            sheet.setColumnWidth(i, 3500);
        }
    }

    private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles, int rowIndex) {
        Font titleFont = wb.createFont();
        titleFont.setFontName("simsun");
        titleFont.setFontHeightInPoints((short) 14);
        titleFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setFont(titleFont);
        setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new java.awt.Color(0, 0, 0)));
        Row titleRow = sheet.createRow(rowIndex);
        int colIndex = 0;
        for (String field : titles) {
            Cell cell = titleRow.createCell(colIndex);
            cell.setCellValue(field);
            cell.setCellStyle(titleStyle);
            colIndex++;
        }
        rowIndex++;
        return rowIndex;
    }

    private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
        int colIndex = 0;
        Font dataFont = wb.createFont();
        dataFont.setFontName("simsun");
        dataFont.setFontHeightInPoints((short) 14);
        dataFont.setColor(IndexedColors.BLACK.index);
        XSSFCellStyle dataStyle = wb.createCellStyle();
        dataStyle.setFont(dataFont);
        setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new java.awt.Color(0, 0, 0)));
        for (List<Object> rowData : rows) {
            Row dataRow = sheet.createRow(rowIndex);
            colIndex = 0;
            for (Object cellData : rowData) {
                Cell cell = dataRow.createCell(colIndex);
                if (cellData != null) {
                    if (cellData instanceof Double) {
                        cell.setCellValue(((Double) cellData).doubleValue());
                    } else {
                        cell.setCellValue(cellData.toString());
                    }

                } else {
                    cell.setCellValue("");
                }

                cell.setCellStyle(dataStyle);
                colIndex++;
            }
            rowIndex++;
        }
        return rowIndex;
    }

    private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
    }

}